import re
import requests
import json
import time
import pymysql
from datetime import date
from decimal import Decimal
from config import Config
from db import MysqlDB, OracleDB
from fastapi import FastAPI, HTTPException, Request, Body

# app = FastAPI()

mysql_db = MysqlDB(host=Config.mysql_host, port=Config.mysql_port, user=Config.mysql_user, password=Config.mysql_password, database=Config.mysql_database)
oracle_db = OracleDB(username=Config.oracle_username, password=Config.oracle_password, dsn=Config.oracle_dsn)


def request_sense(messages, model=Config.sense_model, temperature=0.4, know_ids=["default"]):
    """
    :param messages: list [{"role": "user"/"assistant", "content":"xxx"}]
    :param model:
    :param temperature:
    :param know_ids: 检索的知识库列表,数组元素为知识库返回的 code
    :param local_knows: 知识库文件,本地知识库对象数组, 数组元素为文件名和chunk的数组
    :return: str 大模型结果
    """
    # 使用 async with 语句来创建一个异步客户端实例
    # 定义请求头
    headers = {"Content-Type": "application/json",
                "Authorization": Config.sense_token}
    # 定义请求体（JSON 格式）
    data = {
        "model": model,
        "temperature": temperature,
        "messages": messages,
        "stream": False,
        "know_ids": know_ids,
        "max_new_tokens": 10000
    }
    # 发送 POST 请求，并获取响应
    try:
        with requests.post(Config.sense_url, json=data, headers=headers) as response:
            # 检查响应状态码
            if response.status_code == 200:
                return json.loads(response.content.decode())
            else:
                raise Exception(f"请求错误，状态码：{response.status_code}")
    except Exception as e:
        print("Error:", e)


def read_oracle_sql_file(file_path):
    with open(file_path, 'r', encoding='utf-8') as fr:
        oracle_sql = ''.join(fr.read())
    return oracle_sql


def oracle_to_mysql(oracle_sql):
    pattern = r"```(.*?)```"
    messages = [{"role":"system", "content": Config.sysstem_prompt}, {"role":"user", "content":oracle_sql}]
    llm_ret = request_sense(messages)
    content = llm_ret['data']['choices'][0]['message'].replace("```sql", "```")
    # 查找所有匹配的文本,但只保留一条
    mysql_sql = re.findall(pattern, content, re.DOTALL)[-1]
    return content, mysql_sql


def oracle_mysql_query(oracle_sql, mysql_sql):
    mysql_db.connect()
    oracle_db.connect()

    mysql_ret = mysql_db.query(mysql_sql.strip(";"))
    mysql_db.close()

    oracle_ret = oracle_db.query(oracle_sql.strip(";"))
    oracle_db.close()

    return mysql_ret, oracle_ret


def main(file_path):
    oracle_sql = read_oracle_sql_file(file_path)
    content, mysql_sql = oracle_to_mysql(oracle_sql)
    mysql_ret, oracle_ret = oracle_mysql_query(oracle_sql, mysql_sql)

    print("oracle语句", oracle_sql, sep="\n")
    print("-*-"*10)
    print("大模型结果", content, sep="\n")
    print("-*-"*10)
    print("转化后mysql语句", mysql_sql, sep="\n")
    print("-*-"*10)
    if oracle_ret:
        print("oracle查询结果：", "\n".join(["| " + " | ".join([str(r) for r in ret]) + " |" for ret in oracle_ret]), sep="\n")
        print("-*-"*10)
    else:
        print("oracle查询结果为空")
    if mysql_ret:
        print("mysql查询结果：", "\n".join(["| " + " | ".join([str(r) for r in ret]) + " |" for ret in mysql_ret]), sep="\n")
    else:
        print("mysql查询结果为空")
if __name__ == "__main__":
    import sys
    main(file_path=sys.argv[1])
    